from numpy.lib.function_base import append
from pandas.core.frame import DataFrame
from sklearn.cluster.k_means_ import KMeans, k_means
from city_plan import *
from preprocess import *
import psycopg2
import datetime
import matplotlib.pyplot as plt
import numpy as np
import geopandas as gpd
from json import dumps
import json
from geojson import Point, feature
from geojson import Feature
from geojson import FeatureCollection
import geojson
import pandas as pd
from geoalchemy2 import Geometry, WKTElement
from sqlalchemy import create_engine
from sklearn.cluster import DBSCAN

#从数据库以dataframe形式返回taxi数据
def get_df_from_db_taxi(sql,conn):
    return pd.read_sql(sql,conn)

# 关闭数据库连接
def ClosePostgis(cursor,conn):
    cursor.close()
    conn.close()
    print("数据库关闭！")

#开启数据库连接
def OpenPostgis():
    conn=psycopg2.connect(database="DHM_test",user="postgres",password="LKXQsdy702",host="127.0.0.1", port="5432")
    engine = create_engine('postgresql://postgres:LKXQsdy702@127.0.0.1:5432/DHM_test')#连接数据库
    cursor = conn.cursor()
    sql ='SELECT * FROM public.trackpoints_test;'#读取trackpoints_test数据表中的数据
    return conn,engine,sql,cursor

#从数据库获取taxi数据并进行范围筛选
def getTaxiData(sql,conn,cursor):
    df=get_df_from_db_taxi(sql,conn)#df为从数据库中读取得到的这段时间里的轨迹数据
    df=get_sub_df_by_position(df,[(113.9,30.3),(114.6,30.8)])#去掉不在武汉市区范围内的一些无用数据
    #look_into_df(df)
    ClosePostgis(cursor,conn)
    return df

def GetPickDrop(df):
    pick,drop=getPickAndDrop(df)
    pick=pick.reset_index(drop=True)#上车点提取
    drop=drop.reset_index(drop=True)#下车点提取

    gdf_pick = gpd.GeoDataFrame( #转化为geodataframe形式
        pick, geometry=gpd.points_from_xy(pick['lon'], pick['lat']),crs=4326)
    gdf_drop = gpd.GeoDataFrame( #转化为geodataframe形式
        drop, geometry=gpd.points_from_xy(drop['lon'], drop['lat']),crs=4326)
    return pick,drop,gdf_pick,gdf_drop

def gdf_to_postgis(gdf_pick,gdf_drop,engine):
    start = datetime.datetime.now() 
    gdf_pick.to_postgis('pick_point', engine,index= False,if_exists='replace',dtype={'geom': Geometry('POINT', 4326)})
    gdf_drop.to_postgis('drop_point', engine,index= False,if_exists='replace',dtype={'geom': Geometry('POINT', 4326)})
    #geodataframe存入数据库
    end = datetime.datetime.now()
    print('time cost:',(end - start))


#k均值聚类
def kmeans_pickdrop(pick,drop):
    pick_data=np.array(pick.loc[:,['lon','lat']])
    drop_data=np.array(drop.loc[:,['lon','lat']])
    kmeans=KMeans(n_clusters=15).fit(pick_data)
    center_pick=kmeans.cluster_centers_ #k均值聚类的簇中心点
    pick['K']=kmeans.labels_
    kmeans=KMeans(n_clusters=15).fit(drop_data)
    center_drop=kmeans.cluster_centers_ #k均值聚类的簇中心点
    drop['K']=kmeans.labels_
    pick_kmeans_gpd = gpd.GeoDataFrame(
        pick, geometry=gpd.points_from_xy(pick['lon'], pick['lat']),crs=4326)
    drop_kmeans_gpd = gpd.GeoDataFrame(
        drop, geometry=gpd.points_from_xy(drop['lon'], drop['lat']),crs=4326)
    return pick,drop,pick_kmeans_gpd,drop_kmeans_gpd,center_pick,center_drop

#调用这个页面的所有操作
def CityPlan():
    conn,engine,sql,cursor=OpenPostgis()
    df=getTaxiData(sql,conn,cursor)
    pick,drop,gdf_pick,gdf_drop=GetPickDrop(df)
    pick,drop,pick_kmeans_gpd,drop_kmeans_gpd,center=kmeans_pickdrop(pick,drop)
    #保存为json数据
    features=pick_kmeans_gpd.__geo_interface__
    fileObject=open('./KmeansPoint.geojson','w')#此处路径有错误不知道怎么修改
    fileObject.write(dumps(features,indent=4)+"\n")
    fileObject.close()

def ChooseKmeansCenter():#获得所有点中被选择为K均值聚类中心点的数据
    conn,engine,sql,cursor=OpenPostgis()
    df=getTaxiData(sql,conn,cursor)
    pick,drop,gdf_pick,gdf_drop=GetPickDrop(df)
    pick,drop,pick_kmeans_gpd,drop_kmeans_gpd,center_pick,center_drop=kmeans_pickdrop(pick,drop)
    features=[]
    for i in center_pick:
        lon=i[0]
        lat=i[1]
        m_point=Point((float(lon),float(lat)))
        features.append(Feature(geometry=m_point,properties={'pickAndDrop':'Pick','lon':float(lon),'lat':float(lat)}))
    for i in center_drop:
        lon=i[0]
        lat=i[1]
        m_point=Point((float(lon),float(lat)))
        features.append(Feature(geometry=m_point,properties={'pickAndDrop':'Drop','lon':float(lon),'lat':float(lat)}))
    feature_collection=FeatureCollection(features=features)
    fileObject=open('./KCenterPoint.geojson','w')#此处路径有错误不知道怎么修改
    fileObject.write(dumps(feature_collection,indent=4)+"\n")
    fileObject.close()
ChooseKmeansCenter()